var express = require('express');
var router = express.Router();
var mysql = require('../models/mysql_select');
var util = require('../utils/util');
const credential = require('../credentials');
const fs = require('fs');

/* GET api listing. */
router.get('/', function (req, res, next) {
    let startTime = req.query.startTime;
    let endTime = req.query.endTime;
    let ztlxs = req.query.ztlxs;
    let djmp = util.initData(startTime, endTime);
    mysql.query(
        `
        SELECT Sell_Date, ztlxs, tdlx, ybrs, ygjd, yfje, guest_province, guest_sex, guest_city 
        FROM Report_2019 WHERE Sell_Date >= '${startTime}' and Sell_Date <= '${endTime}' And ztlxs LIKE '${ztlxs}%'
        UNION
        SELECT Sell_Date, ztlxs, tdlx, ybrs, ygjd, yfje, guest_province, guest_sex, guest_city 
        FROM Report_2018 WHERE Sell_Date >= '${startTime}' and Sell_Date <= '${endTime}' And ztlxs LIKE '${ztlxs}%'
        `,
        function (result) {
        let result1 = result.map(v=>{
            return {
                Sell_Date: util.formatTime(v.Sell_Date),
                ztlxs: v.ztlxs,
                tdlx: v.tdlx,
                ybrs: v.ybrs,
                ygjd: v.ygjd,
                yfje: Number(v.yfje.replace(',', '')),
                guest_province: v.guest_province,
                guest_sex: v.guest_sex,
                guest_city: v.guest_city
            }
        });
        result1.forEach(v => {
            if (v["tdlx"].includes("单购门票")) {
                let y = false;
                for (let index = 0; index < credential.dparray.length; index++) {
                    let element = credential.dparray[index];
                    y = util.regFun(element, v, "单购门票", "ygjd", djmp);
                    if (y) {
                        break;
                    }
                }
                if (!y) {
                    djmp[v["Sell_Date"]]["单购门票"]["其他"][0] += v.ybrs;
                    djmp[v["Sell_Date"]]["单购门票"]["其他"][1] += v.yfje
                }
            } else if (v["tdlx"].includes("学生票")) {
                djmp[v["Sell_Date"]]["学生票"][0] += v.ybrs;
                djmp[v["Sell_Date"]]["学生票"][1] += v.yfje
            } else if (v["tdlx"].includes("教师票")) {
                djmp[v["Sell_Date"]]["教师票"][0] += v.ybrs;
                djmp[v["Sell_Date"]]["教师票"][1] += v.yfje
            } else if (v["tdlx"].includes("老年票")) {
                djmp[v["Sell_Date"]]["老年票"][0] += v.ybrs;
                djmp[v["Sell_Date"]]["老年票"][1] += v.yfje
            } else if (v["tdlx"].includes("半票")) {
                djmp[v["Sell_Date"]]["半票"][0] += v.ybrs;
                djmp[v["Sell_Date"]]["半票"][1] += v.yfje
            } else if (v["tdlx"].includes("住宿")) {
                let y = false;
                for (let index = 0; index < credential.ddzs.length; index++) {
                    let element = credential.ddzs[index];
                    y = util.regFun(element, v, "住宿", "tdlx", djmp);
                    if (y) {
                        break;
                    }
                }
                if (!y) {
                    djmp[v["Sell_Date"]]["住宿"]["其他"][0] += v.ybrs;

                    djmp[v["Sell_Date"]]["住宿"]["其他"][1] += v.yfje;

                }

            } else {
                let y = false;
                for (let index = 0; index < credential.tc.length; index++) {
                    let y = false;
                    let element = credential.tc[index];
                    y = util.regFun(element, v, "套餐", "tdlx", djmp);
                    if (y) {
                        break;
                    }
                }
                if (!y) {
                    djmp[v["Sell_Date"]]["套餐"]["其他"][0] += v.ybrs;
                    djmp[v["Sell_Date"]]["套餐"]["其他"][1] += v.yfje
                }
            }

        });
        res.json(djmp)
    })
});
router.post('/nzjyp', function(req, res, next){
    let name = req.body.name;
    let sfz = req.body.sfz;
    mysql.insert('INSERT INTO nzjyp1(xm, sfz) VALUES(?,?)', [name, sfz], function (err) {
        if(err) console.log(err);
        res.json({
            ok: true,
            status: 200,
        })
    })

});

module.exports = router;
